package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import base.DbConnect;
import model.Course;
import model.CourseSelect;

public class CourseSelectDao {
	public static int add(String cou_id, String cla_id) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		int res = -1;
		String sql = "insert into courseselect values (?,?,?)";
		try {
			stmt = (PreparedStatement) conn.prepareStatement(sql);
			stmt.setString(1, cou_id);
			stmt.setString(2, cla_id);
			stmt.setString(3, "0");
			res = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}

	public static ArrayList<CourseSelect> select() {
		Connection conn = DbConnect.getConnect();
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<CourseSelect> arrayList = new ArrayList<CourseSelect>();

		String sql;
		sql = "SELECT * FROM view_course_class";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				CourseSelect courseSelect = new CourseSelect();
				courseSelect.setCou_id(rs.getString("cou_id"));
				courseSelect.setCou_name(rs.getString("cou_name"));
				courseSelect.setCou_credit(rs.getString("cou_credit"));
				courseSelect.setCla_id(rs.getString("cla_id"));
				courseSelect.setIs_select(rs.getInt("is_select"));
				arrayList.add(courseSelect);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return arrayList;
	}

	public static int find(String cou_id, String cla_id) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql;
		sql = "SELECT * FROM view_course_class where cou_id = '" + cou_id + "' and " + " cla_id = '" + cla_id + "'" ;
		try {
			stmt = (PreparedStatement) conn.prepareStatement(sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return 1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}
	public static int updata(String cou_id, String cla_id) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql;
		sql = "update view_course_class where cou_id = ? and cla_id = ?";
		try {
			stmt = (PreparedStatement) conn.prepareStatement(sql);
			stmt.setString(1, cou_id);
			stmt.setString(2, cla_id);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return 1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}

	/**
	 * 删除信息
	 * 
	 * @param department
	 * @return
	 */
	public static int delete(CourseSelect courseSelect) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		int res = -1;
		//DELETE FROM `ams`.`courseselect` WHERE `cla_id` = 'B01012103' AND `cou_id` = 'C010101'
		String sql = "DELETE from courseselect where cla_id = ? and cou_id = ? ";
		try {
			stmt = (PreparedStatement) conn.prepareStatement(sql);
			stmt.setString(1, courseSelect.getCla_id());
			stmt.setString(2, courseSelect.getCou_id());
			res = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return res;
	}

	/**
	 * 更新信息
	 * 
	 * @param department
	 * @return
	 */
	public static int updata(CourseSelect courseSelect) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		int res = -1;
		try {
			String sql = "update view_course_class set cou_name = ? , cou_credit = ? ,cla_id = ? , is_select = ? where cou_id = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, courseSelect.getCou_name());
			stmt.setString(2, courseSelect.getCou_credit());
			stmt.setString(3, courseSelect.getCla_id());
			stmt.setString(4, courseSelect.getCou_id());
			stmt.setInt(5, courseSelect.getIs_select());
			res = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return res;
	}
}
